#include "database.h"
//#include <QDebug>
//#include <QMessageBox>
#include "global.h"

DbManager::DbManager(QObject *parent)
    : QObject{parent}
{}

bool DbManager::initMysql()
{
    m_mysqlConn = QSqlDatabase::addDatabase("QMYSQL", "mysql_conn");
    //    dbConn.setHostName("10.35.19.193");
    //    dbConn.setPort(3336);
    //    dbConn.setDatabaseName("my2");
    //    dbConn.setUserName("root");
    //    dbConn.setPassword("good@2022");

//    mysqlConn.setHostName("10.35.19.121");
//    mysqlConn.setPort(3306);
//    mysqlConn.setDatabaseName("zentao");
//    mysqlConn.setUserName("read");
//    mysqlConn.setPassword("a0gu5TH9lE7MUD1p");
//    qDebug() << mySqlInfo.hostName<<mySqlInfo.port<<mySqlInfo.dbName<<mySqlInfo.userName<<mySqlInfo.password<<mySqlInfo.dbdesc;
    m_mysqlConn.setHostName(mySqlInfo.hostName);
    m_mysqlConn.setPort(mySqlInfo.port.toInt());
    m_mysqlConn.setDatabaseName(mySqlInfo.dbName);
    m_mysqlConn.setUserName(mySqlInfo.userName);
    m_mysqlConn.setPassword(mySqlInfo.password);

    g_mysqlPool.initDbParam(mySqlInfo.hostName,mySqlInfo.port.toInt(),mySqlInfo.dbName,mySqlInfo.userName,mySqlInfo.password,5);
    if (m_mysqlConn.open()) {
        qDebug() << "连接数据库(mysql)成功!";
        return true;
    } else {
        QSqlError err1 = m_mysqlConn.lastError();
        QString errStr = "连接数据库失败! " + err1.text();
        qDebug() << errStr;
        return false;
    }
}

bool DbManager::initSqlite()
{
    m_sqliteConn = QSqlDatabase::addDatabase("QSQLITE", "sqlite_conn");
    m_sqliteConn.setDatabaseName("./dn.db");
    if (m_sqliteConn.open()) {
        QSqlQuery query(m_sqliteConn);
        query.exec("SELECT sqlite_version()");
        QString sqliteVersion;
        if (query.next()) {
            sqliteVersion = query.value(0).toString();
            //qDebug() << "SQLite version: " << sqliteVersion;
        } else {
            qWarning() << "Failed to retrieve SQLite version";
        }
        qDebug() << "连接数据库(sqlite)成功!"<<sqliteVersion;
        return true;
    } else {
        qDebug() << "连接数据库失败! " + m_sqliteConn.lastError().text();;
        return false;
    }
}

bool DbManager::initCfgData()
{
    QSqlQueryModel *model = new QSqlQueryModel;
    model->setQuery("select hostip,hostport, dbname,username,pwd,dbdesc from dbinfo where dbname ='tdomainmgr' and dbtype='mysql' ",
                    QSqlDatabase::database("sqlite_conn"));

    // int column= model->columnCount(); //获得列数
    int row = model->rowCount(); // 获得行数

    for (int i = 0; i < row; i++) {

        mySqlInfo.hostName=model->index(i, 0).data().toString();
        mySqlInfo.port=model->index(i, 1).data().toString();
        mySqlInfo.dbName=model->index(i, 2).data().toString();
        mySqlInfo.userName=model->index(i, 3).data().toString();
        mySqlInfo.password=model->index(i, 4).data().toString();
        mySqlInfo.dbdesc=model->index(i, 5).data().toString();
    }
    //qDebug() << 1<<mySqlInfo.hostName<<mySqlInfo.port<<mySqlInfo.dbName<<mySqlInfo.userName<<mySqlInfo.password<<mySqlInfo.dbdesc;
    return true;
}

int DbManager::getRecTotalCount(QString qryType,QString qryParam)
{
    QString strSql;
    if(qryType == "DomainByCategoryId")
    {
        QSqlQueryModel *dataModel = new QSqlQueryModel;
        strSql="select count(*) from domain_category_relation where Isvalid =1 and CategoryId="+qryParam+" order by id ";
        dataModel->setQuery(strSql, QSqlDatabase::database("mysql_conn"));
        //qDebug()<<"DomainByCategoryId"<<strSql;
        return dataModel->index(0, 0).data().toInt();
    }
    else
    {
        return 0;
    }
}

QString DbManager::getParentPath(int parentId)
{
    QSqlQuery query(m_mysqlConn);
    query.prepare("SELECT path FROM domain_category WHERE id = ?");
    query.addBindValue(parentId);

    if (query.exec() && query.next()) {
        return query.value(0).toString();
    } else {
        qDebug() << "Failed to retrieve path for parent ID: " << parentId;
        return "";
    }
}
QList<DomainInfo> DbManager::getAllDomainRecByCategoryId(int categoryId,int curPage,int pageSize)
{
    QList<DomainInfo> diList;
    diList.clear();
    QSqlQuery query(m_mysqlConn);
    //    QString strSql = "select id,categoryId,domain,registryExpiry,registrarExpiry,createdOn,lastUpdate,lookedUp,apiProfile,ip,whoisServer,registrar from  domain_info WHERE categoryId = "+QString::number(nodeId);
    QString strSql = "SELECT b.domainId,b.DomainName,b.RegistryDomainID,b.RegistrarWHOISServer,b.RegistrarURL,b.UpdatedDate,b.CreationDate,b.RegistryExpiryDate,b.Registrar,b.RegistrarIANAID,b.RegistrarEmail,b.RegistrarPhone,b.RegistryRegistrantID,b.RegistrantName,b.RegistrantOrganization,b.RegistrantStreet,b.RegistrantCity,b.RegistrantStateProvince,b.RegistrantPostalCode,b.RegistrantCountry,b.RegistrantPhone,b.RegistrantFax,b.RegistrantEmail,b.NameServer,b.DomainStatus,b.DNSSEC FROM `domain_category_relation` a, domain_registry_info b where a.categoryId =? and a.id =b.domainId limit ?,?";
    //query.addBindValue(QString::number(nodeId));
    query.prepare(strSql);
    query.addBindValue(categoryId);
    int a =pageSize*(curPage-1);
    int b = pageSize;
    //qDebug()<<a<<b<<curPage<<pageSize;
    query.addBindValue(pageSize*(curPage-1));
    query.addBindValue(pageSize);
    //qDebug()<<strSql;
    if (!query.exec())
    {
        qDebug() << "Failed to execute query: " << query.lastError().text();
        return diList;
    }
    while (query.next())
    {
        DomainInfo di;
        di.Id = query.value(0).toInt();
        di.CategoryId = categoryId;
        di.Domain = query.value(1).toString();
        di.RegistryDomainID = query.value(2).toString();
        di.RegistrarWHOISServer = query.value(3).toString();
        di.RegistrarURL = query.value(4).toString();
        di.UpdatedDate = query.value(5).toString();
        di.CreationDate = query.value(6).toString();
        di.RegistryExpiryDate = query.value(7).toString();
        di.Registrar = query.value(8).toString();
        di.RegistrarIANAID = query.value(9).toString();
        di.RegistrarEmail = query.value(10).toString();
        di.RegistrarPhone = query.value(11).toString();
        di.RegistryRegistrantID = query.value(12).toString();
        di.RegistrantName = query.value(13).toString();
        di.RegistrantOrganization = query.value(14).toString();
        di.RegistrantStreet = query.value(15).toString();
        di.RegistrantCity = query.value(16).toString();
        di.RegistrantStateProvince = query.value(17).toString();
        di.RegistrantPostalCode = query.value(18).toString();
        di.RegistrantCountry = query.value(19).toString();
        di.RegistrantPhone = query.value(20).toString();
        di.RegistrantFax = query.value(21).toString();
        di.RegistrantEmail = query.value(22).toString();
        di.NameServer = query.value(23).toString();
        di.DomainStatus = query.value(24).toString();
        di.DNSSEC = query.value(25).toString();

        diList.append(di);
    }
    //qDebug() << "getDomainInfoById,categoryId:"<<nodeId<<"damain num:"<<diList.size();
    return diList;
}

QVector<DataDict> DbManager::getDataDict() {

    QString whereSql ="";

    QVector<DataDict>  qvdd;
    qvdd.clear();
    QSqlQuery query(m_mysqlConn);

    QString strSql = "SELECT dd_key,dd_value,dd_type,dd_desc FROM datadict "+ whereSql+"  order by dd_type ";//limit 2";
    query.prepare(strSql);

    if (!query.exec())
    {
        qDebug() << "Failed to datadict" << query.lastError().text();
        return qvdd;
    }
    while (query.next())
    {
        DataDict dd;
        dd.dd_key = query.value(0).toString();
        dd.dd_values = query.value(1).toString();
        dd.dd_type = query.value(2).toString();
        dd.dd_desc = query.value(3).toString();
        qvdd.append(dd);
    }
    qDebug() << "success DataDict"<<qvdd.size();
    return qvdd;
}

QList<DnSummaryInfo> DbManager::getDomainRecByDnIds(QString  dnIds )
{
    QList<DnSummaryInfo> diList;
    diList.clear();
    QString strSql,whereSql;

    whereSql = "and dri.id in  ("+dnIds+")  ";
    QSqlQuery query(m_mysqlConn);

    strSql = "select dri.id ,dri.DomainName ,dri.DomainSuffix ,dri.RegistStatus,dri.Registrar ,dri.CreationDate ,dri.RegistryExpiryDate ,dri.UpdatedDate,dri.NameServer ,dri.RegistrantName ,dri.RegistrantOrganization ,dri.RegistrantCountry ,dri.RegistrantEmail ,dri.DomainStatus,dri.LastQryDate  from domain_registry_info dri  where dri.Isvalid =1 "+whereSql +" order by dri.DomainName";
    //qDebug()<<strSql;
    query.prepare(strSql);

    //qDebug()<<categoryId<<filterSuffix<<filterDnStatus<<filterExpDate<<strSql;

    if (!query.exec())
    {
        qDebug() << "Failed to execute query: " << query.lastError().text();
        return diList;
    }
    while (query.next())
    {
        DnSummaryInfo di;
        di.id = query.value(0).toInt();
        di.domain = query.value(1).toString();
        di.suffix = query.value(2).toString();
        di.registStatus = query.value(3).toString();
        di.registrar = query.value(4).toString();
        di.creationDate = query.value(5).toString();
        di.expiryDate = query.value(6).toString();
        di.updatedDate = query.value(7).toString();
        di.nameServer = query.value(8).toString();
        di.registrant = query.value(9).toString();
        di.registrantOrg = query.value(10).toString();
        di.registrantCountry = query.value(11).toString();
        di.registrantEmail = query.value(12).toString();
        di.domainStatus = query.value(13).toString();
        di.lastQryDate = query.value(14).toString();
        diList.append(di);
    }
    return diList;
}

QList<DnSummaryInfo> DbManager::getDomainRecByCategoryId(int categoryId,QString filterSuffix,QString filterDnStatus,QString filterExpDate,QString filterRegStatus)
{
    QList<DnSummaryInfo> diList;
    diList.clear();
    QString strSql,whereSql;
    if(filterSuffix.length())
        whereSql=" and dri.DomainSuffix='"+filterSuffix+"'";
    if(filterDnStatus.length())
        whereSql+=" and dri.DomainStatus like '%"+filterDnStatus+"%'";
    if(filterExpDate.length())
        whereSql+=" and dri.RegistryExpiryDate < '"+filterExpDate+"'";
    if(filterRegStatus.length())
        whereSql+=" and dri.RegistStatus = '"+filterRegStatus+"'";
    QSqlQuery query(m_mysqlConn);
    if(categoryId==0)//获取所有记录
    {
        strSql = "select dri.id ,dri.DomainName ,dri.DomainSuffix ,dri.RegistStatus,dri.Registrar ,dri.CreationDate ,dri.RegistryExpiryDate ,dri.UpdatedDate,dri.NameServer ,dri.RegistrantName ,dri.RegistrantOrganization ,dri.RegistrantCountry ,dri.RegistrantEmail ,dri.DomainStatus,dri.LastQryDate  from domain_registry_info dri  where dri.Isvalid =1 "+whereSql +" order by dri.DomainName";
        query.prepare(strSql);
    }
    else
    {
        strSql = "select dri.id ,dri.DomainName ,dri.DomainSuffix ,dri.RegistStatus,dri.Registrar ,dri.CreationDate ,dri.RegistryExpiryDate ,dri.UpdatedDate,dri.NameServer ,dri.RegistrantName ,dri.RegistrantOrganization ,dri.RegistrantCountry ,dri.RegistrantEmail ,dri.DomainStatus,dri.LastQryDate  from domain_registry_info dri,domain_category_relation dcr  where dcr.categoryId = ? and  dcr.domainid =dri.id and dri.Isvalid =1 "+whereSql +" order by dri.DomainName";
        query.prepare(strSql);
        query.addBindValue(categoryId);
    }
    //qDebug()<<categoryId<<filterSuffix<<filterDnStatus<<filterExpDate<<strSql;

    if (!query.exec())
    {
        qDebug() << "Failed to execute query: " << query.lastError().text();
        return diList;
    }
    while (query.next())
    {
        DnSummaryInfo di;
        di.ifChecked = false;
        di.id = query.value(0).toInt();
        di.domain = query.value(1).toString();
        di.suffix = query.value(2).toString();
        di.registStatus = query.value(3).toString();
        di.registrar = query.value(4).toString();
        di.creationDate = query.value(5).toString();
        di.expiryDate = query.value(6).toString();
        di.updatedDate = query.value(7).toString();
        di.nameServer = query.value(8).toString();
        di.registrant = query.value(9).toString();
        di.registrantOrg = query.value(10).toString();
        di.registrantCountry = query.value(11).toString();
        di.registrantEmail = query.value(12).toString();
        di.domainStatus = query.value(13).toString();
        di.lastQryDate = query.value(14).toString();
        diList.append(di);
    }
    return diList;
}

QList<KeyValue> DbManager::getDomainRegistryById(int domainId)
{
    QList<KeyValue> infoList;
    QSqlQuery query(m_mysqlConn);
    QString strSql ="select domainId,DomainName,RegistryDomainID,RegistrarWHOISServer,RegistrarURL,UpdatedDate,CreationDate,RegistryExpiryDate,Registrar,RegistrarIANAID,RegistrarEmail,RegistrarPhone,RegistryRegistrantID,RegistrantName,RegistrantOrganization,RegistrantStreet,RegistrantCity,RegistrantStateProvince,RegistrantPostalCode,RegistrantCountry,RegistrantPhone,RegistrantFax,RegistrantEmail from domain_registry_info where domainId="+QString::number(domainId);
    query.prepare(strSql);
    //qDebug()<<strSql;
    //query.addBindValue(QString::number(domainId));
    if (!query.exec())
    {
        qDebug() << "Failed to execute query: " << query.lastError().text();
        return infoList;
    }
    query.first();
    //qDebug()<<query.value(2).toString();
    //    infoMaps["Domain ID"]=query.value(0).toString();
    //    infoMaps["Domain Name"]=query.value(1).toString();
    //    infoMaps["Registry Domain ID"]=query.value(2).toString();
    //    infoMaps["Registrar WHOIS Server"]=query.value(3).toString();
    //    infoMaps["Registrar URL"]=query.value(4).toString();
    //    infoMaps["Updated Date"]=query.value(5).toString();
    //    infoMaps["Creation Date"]=query.value(6).toString();
    //    infoMaps["Registry Expiry Date"]=query.value(7).toString();
    //    infoMaps["Registrar"]=query.value(8).toString();
    //    infoMaps["Registrar IANA ID"]=query.value(9).toString();
    //    infoMaps["Registrar Abuse Contact Email"]=query.value(10).toString();
    //    infoMaps["Registrar Abuse Contact Phone"]=query.value(11).toString();
    //    infoMaps["Registry Registrant ID"]=query.value(12).toString();
    //    infoMaps["Registrant Name"]=query.value(13).toString();
    //    infoMaps["Registrant Organization"]=query.value(14).toString();
    //    infoMaps["Registrant Street"]=query.value(15).toString();
    //    infoMaps["Registrant City"]=query.value(16).toString();
    //    infoMaps["Registrant State/Province"]=query.value(17).toString();
    //    infoMaps["Registrant Postal Code"]=query.value(18).toString();
    //    infoMaps["Registrant Country"]=query.value(19).toString();
    //    infoMaps["Registrant Phone"]=query.value(20).toString();
    //    infoMaps["Registrant Fax"]=query.value(21).toString();
    //    infoMaps["Registrant Email"]=query.value(22).toString();

    infoList.append(KeyValue{"Domain ID",query.value(0).toString()});
    infoList.append(KeyValue{"Domain Name",query.value(1).toString()});
    infoList.append(KeyValue{"Registry Domain ID",query.value(2).toString()});
    infoList.append(KeyValue{"Registrar WHOIS Server",query.value(3).toString()});
    infoList.append(KeyValue{"Registrar URL",query.value(4).toString()});
    infoList.append(KeyValue{"Updated Date",query.value(5).toString()});
    infoList.append(KeyValue{"Creation Date",query.value(6).toString()});
    infoList.append(KeyValue{"Registry Expiry Date",query.value(7).toString()});
    infoList.append(KeyValue{"Registrar",query.value(8).toString()});
    infoList.append(KeyValue{"Registrar IANA ID",query.value(9).toString()});
    infoList.append(KeyValue{"Registrar Abuse Contact Email",query.value(10).toString()});
    infoList.append(KeyValue{"Registrar Abuse Contact Phone",query.value(11).toString()});
    infoList.append(KeyValue{"Registry Registrant ID",query.value(12).toString()});
    infoList.append(KeyValue{"Registrant Name",query.value(13).toString()});
    infoList.append(KeyValue{"Registrant Organization",query.value(14).toString()});
    infoList.append(KeyValue{"Registrant Street",query.value(15).toString()});
    infoList.append(KeyValue{"Registrant City",query.value(16).toString()});
    infoList.append(KeyValue{"Registrant State/Province",query.value(17).toString()});
    infoList.append(KeyValue{"Registrant Postal Code",query.value(18).toString()});
    infoList.append(KeyValue{"Registrant Country",query.value(19).toString()});
    infoList.append(KeyValue{"Registrant Phone",query.value(20).toString()});
    infoList.append(KeyValue{"Registrant Fax",query.value(21).toString()});
    infoList.append(KeyValue{"Registrant Email",query.value(22).toString()});

    //qDebug()<<"getDomainRegistryById"<<domainId<<infoMaps.size();
    return infoList;
}

QList<KeyValue> DbManager::getDomainAdminById(int domainId)
{
    QList<KeyValue> infoList;
    QSqlQuery query(m_mysqlConn);
    QString strSql ="select domainId,DomainName,RegistryAdminID,AdminName,AdminOrganization,AdminStreet,AdminCity,AdminStateProvince,AdminPostalCode,AdminCountry,AdminPhone,AdminFax,AdminEmail from domain_admin_info where domainId=?";
    query.prepare(strSql);
    query.addBindValue(domainId);
    if (!query.exec())
    {
        qDebug() << "Failed to execute query: " << query.lastError().text();
        return infoList;
    }
    query.first();
    //qDebug()<<query.value(2).toString();
    //    infoMaps["Domain ID"]=query.value(0).toString();
    //    infoMaps["Domain Name"]=query.value(1).toString();
    //    infoMaps["Registry Admin ID"]=query.value(2).toString();
    //    infoMaps["Admin Name"]=query.value(3).toString();
    //    infoMaps["Admin Organization"]=query.value(4).toString();
    //    infoMaps["Admin Street"]=query.value(5).toString();
    //    infoMaps["Admin City"]=query.value(6).toString();
    //    infoMaps["Admin State/Province"]=query.value(7).toString();
    //    infoMaps["Admin Postal Code"]=query.value(8).toString();
    //    infoMaps["Admin Country"]=query.value(9).toString();
    //    infoMaps["Admin Phone"]=query.value(10).toString();
    //    infoMaps["Admin Fax"]=query.value(11).toString();
    //    infoMaps["Admin Email"]=query.value(12).toString();

    infoList.append(KeyValue{"Domain ID",query.value(0).toString()});
    infoList.append(KeyValue{"Domain Name",query.value(1).toString()});
    infoList.append(KeyValue{"Admin Tech ID",query.value(2).toString()});
    infoList.append(KeyValue{"Admin Name",query.value(3).toString()});
    infoList.append(KeyValue{"Admin Organization",query.value(4).toString()});
    infoList.append(KeyValue{"Admin Street",query.value(5).toString()});
    infoList.append(KeyValue{"Admin City",query.value(6).toString()});
    infoList.append(KeyValue{"Admin State/Province",query.value(7).toString()});
    infoList.append(KeyValue{"Admin Postal Code",query.value(8).toString()});
    infoList.append(KeyValue{"Admin Country",query.value(9).toString()});
    infoList.append(KeyValue{"Admin Phone",query.value(10).toString()});
    infoList.append(KeyValue{"Admin Fax",query.value(11).toString()});
    infoList.append(KeyValue{"Admin Email",query.value(12).toString()});
    //qDebug()<<"getDomainAdminById"<<domainId<<infoMaps.size();
    return infoList;
}

QList<KeyValue> DbManager::getDomainTechById(int domainId)
{
    QList<KeyValue> infoList;
    QSqlQuery query(m_mysqlConn);
    QString strSql ="select domainId,DomainName,RegistryTechID,TechName,TechOrganization,TechStreet,TechCity,TechStateProvince,TechPostalCode,TechCountry,TechPhone,TechFax,TechEmail from domain_tech_info where domainId=?";
    query.prepare(strSql);
    query.addBindValue(domainId);
    if (!query.exec())
    {
        qDebug() << "Failed to execute query: " << query.lastError().text();
        return infoList;
    }
    query.first();
    //qDebug()<<query.value(2).toString();
    //    infoMaps["Domain ID"]=query.value(0).toString();
    //    infoMaps["Domain Name"]=query.value(1).toString();
    //    infoMaps["Registry Tech ID"]=query.value(2).toString();
    //    infoMaps["Tech Name"]=query.value(3).toString();
    //    infoMaps["Tech Organization"]=query.value(4).toString();
    //    infoMaps["Tech Street"]=query.value(5).toString();
    //    infoMaps["Tech City"]=query.value(6).toString();
    //    infoMaps["Tech State/Province"]=query.value(7).toString();
    //    infoMaps["Tech Postal Code"]=query.value(8).toString();
    //    infoMaps["Tech Country"]=query.value(9).toString();
    //    infoMaps["Tech Phone"]=query.value(10).toString();
    //    infoMaps["Tech Fax"]=query.value(11).toString();
    //    infoMaps["Tech Email"]=query.value(12).toString();

    infoList.append(KeyValue{"Domain ID",query.value(0).toString()});
    infoList.append(KeyValue{"Domain Name",query.value(1).toString()});
    infoList.append(KeyValue{"Registry Tech ID",query.value(2).toString()});
    infoList.append(KeyValue{"Tech Name",query.value(3).toString()});
    infoList.append(KeyValue{"Tech Organization",query.value(4).toString()});
    infoList.append(KeyValue{"Tech Street",query.value(5).toString()});
    infoList.append(KeyValue{"Tech City",query.value(6).toString()});
    infoList.append(KeyValue{"Tech State/Province",query.value(7).toString()});
    infoList.append(KeyValue{"Tech Postal Code",query.value(8).toString()});
    infoList.append(KeyValue{"Tech Country",query.value(9).toString()});
    infoList.append(KeyValue{"Tech Phone",query.value(10).toString()});
    infoList.append(KeyValue{"Tech Fax",query.value(11).toString()});
    infoList.append(KeyValue{"Tech Email",query.value(12).toString()});
    //qDebug()<<"getDomainTechById"<<domainId<<infoMaps.size();

    return infoList;
}

QList<KeyValue> DbManager::getDomainBillById(int domainId)
{
    QList<KeyValue> infoList;
    QSqlQuery query(m_mysqlConn);
    QString strSql ="select domainId,DomainName,RegistryBillingID,BillingName,BillingOrganization,BillingStreet,BillingCity,BillingStateProvince,BillingPostalCode,BillingCountry,BillingPhone,BillingFax,BillingEmail from domain_bill_info where domainId=?";
    query.prepare(strSql);
    query.addBindValue(domainId);
    if (!query.exec())
    {
        qDebug() << "Failed to execute query: " << query.lastError().text();
        return infoList;
    }
    query.first();

    infoList.append(KeyValue{"Domain ID",query.value(0).toString()});
    infoList.append(KeyValue{"Domain Name",query.value(1).toString()});
    infoList.append(KeyValue{"Registry Billing ID",query.value(2).toString()});
    infoList.append(KeyValue{"Billing Name",query.value(3).toString()});
    infoList.append(KeyValue{"Billing Organization",query.value(4).toString()});
    infoList.append(KeyValue{"Billing Street",query.value(5).toString()});
    infoList.append(KeyValue{"Billing City",query.value(6).toString()});
    infoList.append(KeyValue{"Billing State/Province",query.value(7).toString()});
    infoList.append(KeyValue{"Billing Postal Code",query.value(8).toString()});
    infoList.append(KeyValue{"Billing Country",query.value(9).toString()});
    infoList.append(KeyValue{"Billing Phone",query.value(10).toString()});
    infoList.append(KeyValue{"Billing Fax",query.value(11).toString()});
    infoList.append(KeyValue{"Billing Email",query.value(12).toString()});

    //    infoList.insert("Domain ID",query.value(0).toString());
    //    infoMaps["Domain ID"]=query.value(0).toString();
    //    infoMaps["Domain Name"]=query.value(1).toString();
    //    infoMaps["Registry Billing ID"]=query.value(2).toString();
    //    infoMaps["Billing Name"]=query.value(3).toString();
    //    infoMaps["Billing Organization"]=query.value(4).toString();
    //    infoMaps["Billing Street"]=query.value(5).toString();
    //    infoMaps["Billing City"]=query.value(6).toString();
    //    infoMaps["Billing State/Province"]=query.value(7).toString();
    //    infoMaps["Billing Postal Code"]=query.value(8).toString();
    //    infoMaps["Billing Country"]=query.value(8).toString();
    //    infoMaps["Billing Phone"]=query.value(10).toString();
    //    infoMaps["Billing Fax"]=query.value(11).toString();
    //    infoMaps["Billing Email"]=query.value(12).toString();

    //qDebug()<<"getDomainBillById"<<domainId<<infoMaps.size();

    return infoList;
}

QVector<QString> DbManager::getDomainStatusById(int domainId)
{
    QVector<QString>  diList;
    diList.clear();
    QSqlQuery query(m_mysqlConn);

    QString strSql = "SELECT domainId,statusInfo FROM domain_status where domainId=?";
    query.prepare(strSql);
    query.addBindValue(domainId);

    //qDebug()<<strSql;
    if (!query.exec())
    {
        qDebug() << "Failed to getDomainStatusById" << query.lastError().text();
        return diList;
    }
    while (query.next())
    {
        diList.append(query.value(1).toString());
    }
    //qDebug() << "getDomainStatusById"<<domainId<<diList.size();
    return diList;
}

QVector<QString> DbManager::getDomainNameServerById(int domainId)
{
    QVector<QString>  diList;
    diList.clear();
    QSqlQuery query(m_mysqlConn);

    QString strSql = "SELECT domainId,nameServerInfo FROM domain_name_server where domainId=?";
    query.prepare(strSql);
    query.addBindValue(domainId);

    //qDebug()<<strSql;
    if (!query.exec())
    {
        qDebug() << "Failed to getDomainNameServerById" << query.lastError().text();
        return diList;
    }
    while (query.next())
    {
        diList.append(query.value(1).toString());
    }
    //qDebug() << "getDomainNameServerById"<<domainId<<diList.size();
    return diList;
}

QVector<ProxyServerData> DbManager::getProxyServers(int proxyStatus)
{
    QString whereSql ="";
    if(proxyStatus==0)//获取全部代理信息
    {
        whereSql="";
    }
    else if(proxyStatus==1)//获取有效代理信息
    {
        whereSql=" where statusStr='NoError' ";
    }
    else if(proxyStatus==2){//获取有错误的代理信息
        whereSql=" where statusStr !='NoError' and  statusStr !='NULL' ";
    }
    else if(proxyStatus==3){//获取未验证代理信息
        whereSql=" where statusStr='NULL' ";
    }

    QVector<ProxyServerData>  qvps;
    qvps.clear();
    QSqlQuery query(m_mysqlConn);

    QString strSql = "SELECT id,proxyaddr,proxytype,statusStr,updatedatetime FROM proxyServers "+ whereSql+" order by proxyaddr ";//limit 2";
    query.prepare(strSql);

    //qDebug()<<strSql;
    if (!query.exec())
    {
        qDebug() << "Failed to getProxyServers" << query.lastError().text();
        return qvps;
    }
    while (query.next())
    {
        ProxyServerData psd;
        psd.proxyaddr = query.value(1).toString();
        //psd.port = query.value(2).toString();
        psd.proxytype = query.value(2).toString();
        psd.updatedatetime = query.value(4).toString();
        psd.statusStr = query.value(3).toString();
        qvps.append(psd);
    }
    //qDebug() << "getProxyServers"<<qvps.size();
    return qvps;
}


bool DbManager::getWhoisServerByDomainSuffix(QVector<DomainQryAddrInfo> &suffixQryAddr)
{
    QSqlQuery query(m_mysqlConn);
    QString strSql = "select domainsuffix,queryaddr,port,dntype  from  whois_query_addr order by  domainsuffix asc";
    query.prepare(strSql);

    if (!query.exec())
    {
        qDebug() << "Failed to execute query whois_query_addr: " << query.lastError().text();
        return false;
    }
    suffixQryAddr.clear();
    while (query.next())
    {
        DomainQryAddrInfo di;
        di.domainSuffix = query.value(0).toString();
        di.qryAddress = query.value(1).toString();
        di.port = query.value(2).toString();
        di.dntype =query.value(3).toString();
        suffixQryAddr.append(di);

    }
    qDebug() << "获取域名和whois服务的映射表，记录数"<<suffixQryAddr.size();
    return true;
}

bool DbManager::getWhoisServerMappingDnSuffix(QMap<QString, QStringList> &dnSuffixMappingWhoIs)
{
    QSqlQuery query(m_mysqlConn);
    QString strSql = "select domainsuffix,queryaddr,port,dntype  from  whois_query_addr order by  domainsuffix asc";
    query.prepare(strSql);

    if (!query.exec())
    {
        qDebug() << "Failed to execute query whois_query_addr: " << query.lastError().text();
        return false;
    }
    dnSuffixMappingWhoIs.clear();
    while (query.next())
    {

        QString strKey = query.value(0).toString();
        QStringList strWhois;
        strWhois.append(query.value(1).toString());
        strWhois.append(query.value(2).toString());
        strWhois.append(query.value(3).toString());
        dnSuffixMappingWhoIs[strKey]=strWhois;
    }
    qDebug() << "获取域名和whois服务的映射表，记录数"<<dnSuffixMappingWhoIs.size();
        return true;
}
void DbManager::loadCategoryTree(QStandardItemModel *model)
{
    //qDebug()<<m_mysqlConn;
    QSqlQuery query(m_mysqlConn);
    query.prepare("SELECT id, name, path FROM domain_category ORDER BY path");

    if (query.exec()) {
        QHash<int, QStandardItem*> nodeMap; // 用于保存每个节点ID对应的QStandardItem对象

        while (query.next())
        {
            int nodeId = query.value(0).toInt();
            QString nodeName = query.value(1).toString();
            QString nodePath = query.value(2).toString();

            QStringList pathParts = nodePath.split('/');
            QStandardItem *parentItem = nullptr;

            // 获取当前节点的父节点
            for (int i = 1; i < pathParts.size() - 1; ++i)
            {
                int parentId = pathParts[i].toInt();

                if (nodeMap.contains(parentId))
                {
                    parentItem = nodeMap[parentId];
                }
                else
                {
                    qDebug() << "Invalid parent ID detected, skipping node: " << nodeId;
                    continue;
                }
            }

            // 创建并添加新的节点
            QStandardItem *nodeItem = new QStandardItem(nodeName);
            nodeItem->setData(nodeId, Qt::UserRole); // 存储节点ID供后续操作使用
            if (parentItem) {
                nodeItem->setIcon(QIcon(":Resource/icon/bill.svg"));
                parentItem->appendRow(nodeItem);
            } else { // 如果是根节点，将其添加至模型的根项

                if(nodeName=="未分类域名")
                    g_undefineCategoryId=nodeId;
                nodeItem->setIcon(QIcon(":Resource/icon/bookmark-one.svg"));
                model->invisibleRootItem()->appendRow(nodeItem);

            }

            nodeMap[nodeId] = nodeItem; // 将新节点添加到映射表中
        }
    }
    else
    {
        qDebug() << "Failed to execute query: " << query.lastError().text();
    }
}

int DbManager::insertCategoryNode(const QString nodePath, const QString nodeName)
{
    QSqlQuery insertQuery(m_mysqlConn);
    insertQuery.prepare("INSERT INTO domain_category (name, path) VALUES (?, ?)");
    insertQuery.addBindValue(nodeName);
    insertQuery.addBindValue("-");
    if (insertQuery.exec())
    {
        int insertedId = insertQuery.lastInsertId().toInt();
        QString newNodePath = nodePath + '/' + QString::number(insertedId);
        // 更新新节点的完整路径
        QSqlQuery updateQuery(m_mysqlConn);
        updateQuery.prepare("UPDATE domain_category SET path = ? WHERE id = ?");
        updateQuery.addBindValue(newNodePath);
        updateQuery.addBindValue(insertedId);
        if (updateQuery.exec())
            return insertedId;
        else
        {
            qDebug() << "Failed to update path of new node: " << updateQuery.lastError().text();
            return -1;
        }
    }
    else
    {
        qDebug() << "Failed to insert new node: " << insertQuery.lastError().text();
        return -1;
    }
}

bool DbManager::insertDomainRecToTables(int categoryId, QStringList domainStrings)
{
    /*------------------将域名和域名类别ID批插入表中-------*/
    QVariantList ids,valids;
    QVariantList nameStrs;
    for(int i=0;i<domainStrings.size();i++)
    {
        ids<<categoryId;
        valids<<1;
        nameStrs<<domainStrings.at(i);
    }

    QSqlQuery query(m_mysqlConn);
    query.prepare("INSERT INTO domain_category_relation (categoryId,domain,Isvalid) VALUES (?, ?,?)");
    query.addBindValue(ids);
    query.addBindValue(nameStrs);
    query.addBindValue(valids);
    if (!query.execBatch())
    {
        qDebug() << "Failed to insertDomainInfoRec: " << query.lastError().text();
        return false;
    }
    /*------------------获取插入域名信息的实际域名ID和域名名称-------*/
    query.prepare("select id,domain from domain_category_relation where categoryId =?");
    query.addBindValue(categoryId);
    if (!query.exec())
    {
        qDebug() << "Failed to getDomainInfo: " << query.lastError().text();
        return false;
    }
    QVariantList dids;
    QVariantList dnameStrs;
    while (query.next())
    {
        dids << query.value(0).toInt();
        dnameStrs << query.value(1).toString();
    }
    /*------------------将域名ID和域名批量插入各个域名信息分表中-------*/
    query.prepare("INSERT INTO domain_registry_info (domainId,DomainName) VALUES (?, ?)");
    query.addBindValue(dids);
    query.addBindValue(dnameStrs);
    if (!query.execBatch())
    {
        qDebug() << "Failed to domain_registry_info: " << query.lastError().text();
        return false;
    }

    query.prepare("INSERT INTO domain_admin_info (domainId,DomainName) VALUES (?, ?)");
    query.addBindValue(dids);
    query.addBindValue(dnameStrs);
    if (!query.execBatch())
    {
        qDebug() << "Failed to domain_admin_info: " << query.lastError().text();
        return false;
    }

    query.prepare("INSERT INTO domain_tech_info (domainId,DomainName) VALUES (?, ?)");
    query.addBindValue(dids);
    query.addBindValue(dnameStrs);
    if (!query.execBatch())
    {
        qDebug() << "Failed to domain_tech_info: " << query.lastError().text();
        return false;
    }

    query.prepare("INSERT INTO domain_bill_info (domainId,DomainName) VALUES (?, ?)");
    query.addBindValue(dids);
    query.addBindValue(dnameStrs);
    if (!query.execBatch())
    {
        qDebug() << "Failed to domain_bill_info: " << query.lastError().text();
        return false;
    }

    return true;
}
bool DbManager::insertDomainRegistry(int domainId, QString domainName, QMap<QString, QString> infoMaps)
{
    qDebug()<<domainId<<domainName;

    QSqlQuery query(m_mysqlConn);
    query.prepare("INSERT INTO  domain_registry_info(domainId,DomainName,RegistryDomainID,RegistrarWHOISServer,RegistrarURL,UpdatedDate,CreationDate,RegistryExpiryDate,Registrar,RegistrarIANAID,RegistrarEmail,RegistrarPhone,RegistryRegistrantID,RegistrantName,RegistrantOrganization,RegistrantStreet,RegistrantCity,RegistrantStateProvince,RegistrantPostalCode,RegistrantCountry,RegistrantPhone,RegistrantFax,RegistrantEmail) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?, ?,?,?,?,?,?)");
    query.addBindValue(domainId);
    query.addBindValue(domainName);
    query.addBindValue(infoMaps["Registry Domain ID"]);
    query.addBindValue(infoMaps["Registrar WHOIS Server"]);
    query.addBindValue(infoMaps["Registrar URL"]);
    query.addBindValue(infoMaps["Updated Date"]);
    query.addBindValue(infoMaps["Creation Date"]);
    query.addBindValue(infoMaps["Registry Expiry Date"]);
    query.addBindValue(infoMaps["Registrar"]);
    query.addBindValue(infoMaps["Registrar IANA ID"]);
    query.addBindValue(infoMaps["Registrar Abuse Contact Email"]);
    query.addBindValue(infoMaps["Registrar Abuse Contact Phone"]);
    query.addBindValue(infoMaps["Registry Registrant ID"]);
    query.addBindValue(infoMaps["Registrant Name"]);
    query.addBindValue(infoMaps["Registrant Organization"]);
    query.addBindValue(infoMaps["Registrant Street"]);
    query.addBindValue(infoMaps["Registrant City"]);
    query.addBindValue(infoMaps["Registrant State/Province"]);
    query.addBindValue(infoMaps["Registrant Postal Code"]);
    query.addBindValue(infoMaps["Registrant Country"]);
    query.addBindValue(infoMaps["Registrant Phone"]);
    query.addBindValue(infoMaps["Registrant Fax"]);
    query.addBindValue(infoMaps["Registrant Email"]);

    if (!query.exec())
    {
        qDebug() << "Failed to insertDomainRegistryInfo: " << query.lastError().text();
        return false;
    }
    return true;
}
bool DbManager::insertDomainStatus(int domainId, QString domainName,QVector<QString> statusVectors)
{
    //qDebug()<<"insertDomainStatus"<<domainId<<domainName;

    QSqlQuery query(m_mysqlConn);
    query.prepare("delete from  domain_status where domainId=?");
    query.addBindValue(domainId);
    if (!query.exec())
    {
        qDebug() << "Failed to deleteDomainStatus: " << query.lastError().text();
        return false;
    }

    query.prepare("INSERT INTO  domain_status(domainId,statusInfo) VALUES (?,?)");

    QVariantList ids;
    QVariantList statuss;
    for(int i=0;i<statusVectors.size();i++)
    {
        ids<<domainId;
        statuss<<statusVectors.at(i);
    }
    query.addBindValue(ids);
    query.addBindValue(statuss);

    if (!query.execBatch())
    {
        qDebug() << "Failed to insertDomainStatus: " << query.lastError().text();
        return false;
    }
    qDebug()<<"insertDomainStatus success"<<domainId<<domainName;
    return true;
}

bool DbManager::insertDomainNameServer(int domainId, QString domainName, QVector<QString> nameServerVectors)
{
    //qDebug()<<"insertDomainNameServer"<<domainId<<domainName;

    QSqlQuery query(m_mysqlConn);

    query.prepare("delete from  domain_name_server where domainId=?");
    query.addBindValue(domainId);
    if (!query.exec())
    {
        qDebug() << "Failed to deleteDomainStatus: " << query.lastError().text();
        return false;
    }

    query.prepare("INSERT INTO  domain_name_server(domainId,nameServerInfo) VALUES (?,?)");

    QVariantList ids;
    QVariantList nameServers;
    for(int i=0;i<nameServerVectors.size();i++)
    {
        ids<<domainId;
        nameServers<<nameServerVectors.at(i);
    }
    query.addBindValue(ids);
    query.addBindValue(nameServers);

    if (!query.execBatch())
    {
        qDebug() << "Failed to insertDomainStatus: " << query.lastError().text();
        return false;
    }
    qDebug()<<"insertDomainStatus success"<<domainId<<domainName;
    return true;
}


bool DbManager::insertDomainAdmin(int domainId, QString domainName, QMap<QString, QString> infoMaps)
{
    qDebug()<<domainId<<domainName;

    QSqlQuery query(m_mysqlConn);
    query.prepare("INSERT INTO  domain_admin_info(domainId,DomainName,RegistryAdminID,AdminName,AdminOrganization,AdminStreet,AdminCity,AdminStateProvince,AdminPostalCode,AdminCountry,AdminPhone,AdminFax,AdminEmail) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?)");
    query.addBindValue(domainId);
    query.addBindValue(domainName);
    query.addBindValue(infoMaps["Registry Admin ID"]);
    query.addBindValue(infoMaps["Admin Name"]);
    query.addBindValue(infoMaps["Admin Organization"]);
    query.addBindValue(infoMaps["Admin Street"]);
    query.addBindValue(infoMaps["Admin City"]);
    query.addBindValue(infoMaps["Admin State/Province"]);
    query.addBindValue(infoMaps["Admin Postal Code"]);
    query.addBindValue(infoMaps["Admin Country"]);
    query.addBindValue(infoMaps["Admin Phone"]);
    query.addBindValue(infoMaps["Admin Fax"]);
    query.addBindValue(infoMaps["Admin Email"]);

    if (!query.exec())
    {
        qDebug() << "Failed to insertDomainAdminInfo: " << query.lastError().text();
        return false;
    }
    return true;
}

bool DbManager::insertDomainTech(int domainId, QString domainName, QMap<QString, QString> infoMaps)
{
    qDebug()<<domainId<<domainName;

    QSqlQuery query(m_mysqlConn);
    query.prepare("INSERT INTO  domain_tech_info(domainId,DomainName,RegistryTechID,TechName,TechOrganization,TechStreet,TechCity,TechStateProvince,TechPostalCode,TechCountry,TechPhone,TechFax,TechEmail) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?)");
    query.addBindValue(domainId);
    query.addBindValue(domainName);
    query.addBindValue(infoMaps["Registry Tech ID"]);
    query.addBindValue(infoMaps["Tech Name"]);
    query.addBindValue(infoMaps["Tech Organization"]);
    query.addBindValue(infoMaps["Tech Street"]);
    query.addBindValue(infoMaps["Tech City"]);
    query.addBindValue(infoMaps["Tech State/Province"]);
    query.addBindValue(infoMaps["Tech Postal Code"]);
    query.addBindValue(infoMaps["Tech Country"]);
    query.addBindValue(infoMaps["Tech Phone"]);
    query.addBindValue(infoMaps["Tech Fax"]);
    query.addBindValue(infoMaps["Tech Email"]);

    if (!query.exec())
    {
        qDebug() << "Failed to insertDomainTechInfo: " << query.lastError().text();
        return false;
    }
    return true;
}

bool DbManager::insertDomainBill(int domainId, QString domainName, QMap<QString, QString> infoMaps)
{
    qDebug()<<domainId<<domainName;

    QSqlQuery query(m_mysqlConn);
    query.prepare("INSERT INTO  domain_bill_info(domainId,DomainName,RegistryBillingID,BillingName,BillingOrganization,BillingStreet,BillingCity,BillingStateProvince,BillingPostalCode,BillingCountry,BillingPhone,BillingFax,BillingEmail,DNSSEC) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
    query.addBindValue(domainId);
    query.addBindValue(domainName);
    query.addBindValue(infoMaps["Registry Billing ID"]);
    query.addBindValue(infoMaps["Billing Name"]);
    query.addBindValue(infoMaps["Billing Organization"]);
    query.addBindValue(infoMaps["Billing Street"]);
    query.addBindValue(infoMaps["Billing City"]);
    query.addBindValue(infoMaps["Billing State/Province"]);
    query.addBindValue(infoMaps["Billing Postal Code"]);
    query.addBindValue(infoMaps["Billing Country"]);
    query.addBindValue(infoMaps["Billing Phone"]);
    query.addBindValue(infoMaps["Billing Fax"]);
    query.addBindValue(infoMaps["Billing Email"]);
    query.addBindValue(infoMaps["DNSSEC"]);
    if (!query.exec())
    {
        qDebug() << "Failed to insertDomainBillInfo: " << query.lastError().text();
        return false;
    }
    return true;
}
bool DbManager::insertDomainRecToSingleTables(int categoryId, QString dnStr)
{
    QString suffix = "."+dnStr.section('.',1,-1);
    QSqlQuery query(m_mysqlConn);
    query.prepare("INSERT INTO domain_registry_info (DomainName,DomainSuffix,Isvalid) VALUES (?,?,?)");
    query.addBindValue(dnStr);
    query.addBindValue(suffix);
    query.addBindValue(1);
    if (!query.exec())
    {
        qDebug() << "Failed to domain_registry_info: " << query.lastError().text();
        return false;
    }
    int domainid = query.lastInsertId().toInt();
    query.prepare("INSERT INTO domain_category_relation (categoryId,domainId) VALUES (?, ?)");
    query.addBindValue(categoryId);
    query.addBindValue(domainid);

    if (!query.exec())
    {
        qDebug() << "Failed to domain_category_relation: " << categoryId<<domainid<<query.lastError().text();
        return false;
    }

    //    /*------------------将域名和域名类别ID批插入表中-------*/
    //    QVariantList ids,valids;
    //    QVariantList nameStrs;
    //    for(int i=0;i<domainStrings.size();i++)
    //    {
    //        ids<<categoryId;
    //        valids<<1;
    //        nameStrs<<domainStrings.at(i);
    //    }

    //    //QSqlQuery query(m_mysqlConn);
    //    query.prepare("INSERT INTO domain_category_relation (categoryId,domain,Isvalid) VALUES (?, ?,?)");
    //    query.addBindValue(ids);
    //    query.addBindValue(nameStrs);
    //    query.addBindValue(valids);
    //    if (!query.execBatch())
    //    {
    //        qDebug() << "Failed to insertDomainInfoRec: " << query.lastError().text();
    //        return false;
    //    }
    //    /*------------------获取插入域名信息的实际域名ID和域名名称-------*/
    //    query.prepare("select id,domain from domain_category_relation where categoryId =?");
    //    query.addBindValue(categoryId);
    //    if (!query.exec())
    //    {
    //        qDebug() << "Failed to getDomainInfo: " << query.lastError().text();
    //        return false;
    //    }
    //    QVariantList dids;
    //    QVariantList dnameStrs;
    //    while (query.next())
    //    {
    //        dids << query.value(0).toInt();
    //        dnameStrs << query.value(1).toString();
    //    }
    //    /*------------------将域名ID和域名批量插入各个域名信息分表中-------*/
    //    query.prepare("INSERT INTO domain_registry_info (domainId,DomainName) VALUES (?, ?)");
    //    query.addBindValue(dids);
    //    query.addBindValue(dnameStrs);
    //    if (!query.execBatch())
    //    {
    //        qDebug() << "Failed to domain_registry_info: " << query.lastError().text();
    //        return false;
    //    }

    //    query.prepare("INSERT INTO domain_admin_info (domainId,DomainName) VALUES (?, ?)");
    //    query.addBindValue(dids);
    //    query.addBindValue(dnameStrs);
    //    if (!query.execBatch())
    //    {
    //        qDebug() << "Failed to domain_admin_info: " << query.lastError().text();
    //        return false;
    //    }

    //    query.prepare("INSERT INTO domain_tech_info (domainId,DomainName) VALUES (?, ?)");
    //    query.addBindValue(dids);
    //    query.addBindValue(dnameStrs);
    //    if (!query.execBatch())
    //    {
    //        qDebug() << "Failed to domain_tech_info: " << query.lastError().text();
    //        return false;
    //    }

    //    query.prepare("INSERT INTO domain_bill_info (domainId,DomainName) VALUES (?, ?)");
    //    query.addBindValue(dids);
    //    query.addBindValue(dnameStrs);
    //    if (!query.execBatch())
    //    {
    //        qDebug() << "Failed to domain_bill_info: " << query.lastError().text();
    //        return false;
    //    }

    return true;
}
bool DbManager::insertBatchDomainRecToSingleTables(int categoryId, QStringList dnStrs)
{
    //qDebug()<<111<<dnStrs.size();
    // 开始事务
    if (!m_mysqlConn.transaction()) {
        qDebug() << "Failed to start transaction:" << m_mysqlConn.lastError().text();
        return false;
    }

    // 预处理SQL插入语句
    QSqlQuery query(m_mysqlConn);
    //query.prepare("INSERT INTO table1 (id, name) VALUES (?, ?) ON CONFLICT DO NOTHING");
    query.prepare("INSERT INTO domain_registry_info (DomainName,DomainSuffix,Isvalid) VALUES (?,?,1) ON CONFLICT DO NOTHING");
    // 执行批处理
    QString suffix;

    for (const auto &data : dnStrs) {
        query.prepare("INSERT INTO domain_registry_info (DomainName,DomainSuffix,Isvalid) VALUES (?,?,1) ");
        suffix="."+data.section('.',1,-1);
        query.addBindValue(data);
        query.addBindValue(suffix);
        if (!query.exec()) {
            // 检查是否为约束错误（例如唯一键冲突）
            if (query.lastError().nativeErrorCode().contains("2067")) { // SQLite的唯一键冲突错误码为2067
                //qDebug() << "2067";
                continue; // 忽略这个错误并继续下一个插入
            } else {
                qDebug() << "Failed to execute batch insert:" << query.lastError().text();
                return false;
            }
        }

        // 获取并处理新插入行的ID
        qint64 lastRowId = query.lastInsertId().toLongLong();
        query.clear();

        // 使用lastRowId插入另一张表，例如table2
        query.prepare("INSERT INTO domain_category_relation (categoryId,domainId) VALUES (?, ?)  ON CONFLICT DO NOTHING");
        query.addBindValue(categoryId);
        query.addBindValue(lastRowId);
        if (!query.exec()) {
            // 类似地，这里也可以根据需要检查错误类型并决定是否继续
            qDebug() << "Failed to insert into table2:" << query.lastError().text();
        }
        query.clear();

    }

    // 提交事务
    if (!m_mysqlConn.commit()) {
        qDebug() << "Failed to commit transaction:" << m_mysqlConn.lastError().text();
        return false;
    }

    return true;
}
//where (path like '%/42/%') or  (path like '%/42')
void DbManager::deleteCategoryNode(int nodeId)
{
    QSqlQuery deleteQuery(m_mysqlConn);
    QString strSql = "DELETE FROM domain_category WHERE (path like '%/"+QString::number(nodeId)+"/%') or (path like '%/"+QString::number(nodeId)+"')";
    qDebug()<<strSql;
    deleteQuery.prepare(strSql);
    //deleteQuery.addBindValue(nodeId);
    if (!deleteQuery.exec())
    {
        qDebug() << "Failed to delete Category: " << deleteQuery.lastError().text()<<nodeId;
    }
}

bool DbManager::deleteDomainRecFromTable(int domainId)
{
    QSqlQuery query(m_mysqlConn);
    query.prepare("update domain_category_relation set Isvalid =0  where id=?");
    query.addBindValue(domainId);
    if (!query.exec())
    {
        qDebug() << "Failed to deleteDomainInfoRec: " << query.lastError().text();
        return false;
    }
    return true;
}

bool DbManager::deleteInvalidProxy()
{
    QSqlQuery query(m_mysqlConn);
    query.prepare("delete from  proxyServers  where  statusStr !='NoError' and  statusStr !='NULL'");

    if (!query.exec())
    {
        qDebug() << "Failed to deleteInvalidProxy: " << query.lastError().text();
        return false;
    }
    return true;
}

bool DbManager::deleteDomainRecFromTable(QVariantList domainIds)
{
    QSqlQuery query(m_mysqlConn);
    //QVariantList dnids = QVariantList::fromList(domainIds);
    //query.prepare("update domain_registry_info set Isvalid =0  where id=?");
    query.prepare("delete from  domain_registry_info where id=?");
    query.addBindValue(domainIds);
    if (!query.execBatch())
    {
        qDebug() << "Failed to delete domain_registry_info: " << query.lastError().text();
        return false;
    }
    query.prepare("delete from  domain_category_relation where domainid=?");
    query.addBindValue(domainIds);
    if (!query.execBatch())
    {
        qDebug() << "Failed to delete domain_category_relation: " << query.lastError().text();
        return false;
    }

    return true;
}

void DbManager::updateCategoryNode(int nodeId, QString nodeName)
{
    QSqlQuery updateQuery(m_mysqlConn);
    updateQuery.prepare("UPDATE domain_category SET name = ? WHERE id = ?");
    updateQuery.addBindValue(nodeName);
    updateQuery.addBindValue(nodeId);
    if (!updateQuery.exec())
    {
        qDebug() << "Failed to update TreeNode: " << updateQuery.lastError().text()<<nodeName;
    }
}




bool DbManager::updateDomainRegistryById(int domainId, QString domainName, QMap<QString, QString> infoMaps)
{


    QSqlQuery query(m_mysqlConn);
    query.prepare("update domain_registry_info set RegistryDomainID=?,RegistrarWHOISServer=?,RegistrarURL=?,UpdatedDate=?,CreationDate=?,RegistryExpiryDate=?,Registrar=?,RegistrarIANAID=?,RegistrarEmail=?,RegistrarPhone=?,RegistryRegistrantID=?,RegistrantName=?,RegistrantOrganization=?,RegistrantStreet=?,RegistrantCity=?,RegistrantStateProvince=?,RegistrantPostalCode=?,RegistrantCountry=?,RegistrantPhone=?,RegistrantFax=?,RegistrantEmail=? where domainId =?");

    query.addBindValue(infoMaps["Registry Domain ID"]);
    query.addBindValue(infoMaps["Registrar WHOIS Server"]);
    query.addBindValue(infoMaps["Registrar URL"]);
    query.addBindValue(infoMaps["Updated Date"]);
    query.addBindValue(infoMaps["Creation Date"]);
    query.addBindValue(infoMaps["Registry Expiry Date"]);
    query.addBindValue(infoMaps["Registrar"]);
    query.addBindValue(infoMaps["Registrar IANA ID"]);
    query.addBindValue(infoMaps["Registrar Abuse Contact Email"]);
    query.addBindValue(infoMaps["Registrar Abuse Contact Phone"]);
    query.addBindValue(infoMaps["Registry Registrant ID"]);
    query.addBindValue(infoMaps["Registrant Name"]);
    query.addBindValue(infoMaps["Registrant Organization"]);
    query.addBindValue(infoMaps["Registrant Street"]);
    query.addBindValue(infoMaps["Registrant City"]);
    query.addBindValue(infoMaps["Registrant State/Province"]);
    query.addBindValue(infoMaps["Registrant Postal Code"]);
    query.addBindValue(infoMaps["Registrant Country"]);
    query.addBindValue(infoMaps["Registrant Phone"]);
    query.addBindValue(infoMaps["Registrant Fax"]);
    query.addBindValue(infoMaps["Registrant Email"]);
    query.addBindValue(domainId);

    if (!query.exec())
    {
        qDebug() << "Failed to updateDomainRegistryInfo: " << query.lastError().text();
        return false;
    }
    qDebug()<<"updateDomainRegistryInfo success"<<domainId<<domainName;
    return true;
}

bool DbManager::updateDomainAdminById(int domainId, QString domainName, QMap<QString, QString> infoMaps)
{
    //qDebug()<<"updateDomainAdminInfo"<<domainId<<domainName;

    QSqlQuery query(m_mysqlConn);
    query.prepare("update domain_admin_info set RegistryAdminID=?,AdminName=?,AdminOrganization=?,AdminStreet=?,AdminCity=?,AdminStateProvince=?,AdminPostalCode=?,AdminCountry=?,AdminPhone=?,AdminFax=?,AdminEmail=? where domainId =?");
    query.addBindValue(infoMaps["Registry Admin ID"]);
    query.addBindValue(infoMaps["Admin Name"]);
    query.addBindValue(infoMaps["Admin Organization"]);
    query.addBindValue(infoMaps["Admin Street"]);
    query.addBindValue(infoMaps["Admin City"]);
    query.addBindValue(infoMaps["Admin State/Province"]);
    query.addBindValue(infoMaps["Admin Postal Code"]);
    query.addBindValue(infoMaps["Admin Country"]);
    query.addBindValue(infoMaps["Admin Phone"]);
    query.addBindValue(infoMaps["Admin Fax"]);
    query.addBindValue(infoMaps["Admin Email"]);
    query.addBindValue(domainId);

    if (!query.exec())
    {
        qDebug() << "Failed to updateDomainAdminInfo: " << query.lastError().text();
        return false;
    }
    qDebug()<<"updateDomainAdminInfo success"<<domainId<<domainName;
    return true;
}

bool DbManager::updateDomainTechById(int domainId, QString domainName, QMap<QString, QString> infoMaps)
{
    //qDebug()<<"updateDomainTechInfo"<<domainId<<domainName;

    QSqlQuery query(m_mysqlConn);
    query.prepare("update domain_tech_info set RegistryTechID=?,TechName=?,TechOrganization=?,TechStreet=?,TechCity=?,TechStateProvince=?,TechPostalCode=?,TechCountry=?,TechPhone=?,TechFax=?,TechEmail=? where domainId =?");

    query.addBindValue(infoMaps["Registry Tech ID"]);
    query.addBindValue(infoMaps["Tech Name"]);
    query.addBindValue(infoMaps["Tech Organization"]);
    query.addBindValue(infoMaps["Tech Street"]);
    query.addBindValue(infoMaps["Tech City"]);
    query.addBindValue(infoMaps["Tech State/Province"]);
    query.addBindValue(infoMaps["Tech Postal Code"]);
    query.addBindValue(infoMaps["Tech Country"]);
    query.addBindValue(infoMaps["Tech Phone"]);
    query.addBindValue(infoMaps["Tech Fax"]);
    query.addBindValue(infoMaps["Tech Email"]);
    query.addBindValue(domainId);
    if (!query.exec())
    {
        qDebug() << "Failed to updateDomainTechInfo: " << query.lastError().text();
        return false;
    }
    qDebug()<<"updateDomainTechInfo success"<<domainId<<domainName;
    return true;
}

bool DbManager::updateDomainBillById(int domainId, QString domainName, QMap<QString, QString> infoMaps)
{
    //qDebug()<<"updateDomainBillInfo"<<domainId<<domainName;

    QSqlQuery query(m_mysqlConn);
    query.prepare("update domain_bill_info set RegistryBillingID=?,BillingName=?,BillingOrganization=?,BillingStreet=?,BillingCity=?,BillingStateProvince=?,BillingPostalCode=?,BillingCountry=?,BillingPhone=?,BillingFax=?,BillingEmail=?,DNSSEC=? where domainId =?");

    query.addBindValue(infoMaps["Registry Billing ID"]);
    query.addBindValue(infoMaps["Billing Name"]);
    query.addBindValue(infoMaps["Billing Organization"]);
    query.addBindValue(infoMaps["Billing Street"]);
    query.addBindValue(infoMaps["Billing City"]);
    query.addBindValue(infoMaps["Billing State/Province"]);
    query.addBindValue(infoMaps["Billing Postal Code"]);
    query.addBindValue(infoMaps["Billing Country"]);
    query.addBindValue(infoMaps["Billing Phone"]);
    query.addBindValue(infoMaps["Billing Fax"]);
    query.addBindValue(infoMaps["Billing Email"]);
    query.addBindValue(infoMaps["DNSSEC"]);
    query.addBindValue(domainId);
    if (!query.exec())
    {
        qDebug() << "Failed to updateDomainBillInfo: " << query.lastError().text();
        return false;
    }
    qDebug()<<"updateDomainBillInfo success"<<domainId<<domainName;
    return true;
}

bool DbManager::updateDomainDetailInfoById(int domainId, QString domainName, DomainInfo dnInfo)
{
    QSqlQuery query(m_mysqlConn);
    query.prepare("update domain_registry_info set DomainSuffix=?, RegistStatus=?,RegistryDomainID=?,RegistrarWHOISServer=?,RegistrarURL=?,UpdatedDate=?,CreationDate=?,RegistryExpiryDate=?,Registrar=?,RegistrarIANAID=?,RegistrarEmail=?,RegistrarPhone=?,RegistryRegistrantID=?,RegistrantName=?,RegistrantOrganization=?,RegistrantStreet=?,RegistrantCity=?,RegistrantStateProvince=?,RegistrantPostalCode=?,RegistrantCountry=?,RegistrantPhone=?,RegistrantFax=?,RegistrantEmail=?,NameServer=?,DomainStatus=?,DNSSEC=?,LastQryDate=?  where id =?");

    query.addBindValue(dnInfo.Suffix);
    query.addBindValue(dnInfo.RegistStatus);
    query.addBindValue(dnInfo.RegistryDomainID);
    query.addBindValue(dnInfo.RegistrarWHOISServer);
    query.addBindValue(dnInfo.RegistrarURL);
    query.addBindValue(dnInfo.UpdatedDate.left(10));
    query.addBindValue(dnInfo.CreationDate.left(10));
    query.addBindValue(dnInfo.RegistryExpiryDate.left(10));
    query.addBindValue(dnInfo.Registrar);
    query.addBindValue(dnInfo.RegistrarIANAID);
    query.addBindValue(dnInfo.RegistrarEmail);
    query.addBindValue(dnInfo.RegistrarPhone);
    query.addBindValue(dnInfo.RegistryRegistrantID);
    query.addBindValue(dnInfo.RegistrantName);
    query.addBindValue(dnInfo.RegistrantOrganization);
    query.addBindValue(dnInfo.RegistrantStreet);
    query.addBindValue(dnInfo.RegistrantCity);
    query.addBindValue(dnInfo.RegistrantStateProvince);
    query.addBindValue(dnInfo.RegistrantPostalCode);
    query.addBindValue(dnInfo.RegistrantCountry);
    query.addBindValue(dnInfo.RegistrantPhone);
    query.addBindValue(dnInfo.RegistrantFax);
    query.addBindValue(dnInfo.RegistrantEmail);
    query.addBindValue(dnInfo.NameServer);
    query.addBindValue(dnInfo.DomainStatus);
    query.addBindValue(dnInfo.DNSSEC);
    query.addBindValue(dnInfo.LastQryDate);
    query.addBindValue(domainId);

    if (!query.exec())
    {
        qDebug() << "Failed to updateDomainRegistryInfo: " << query.lastError().text();
        return false;
    }
    //qDebug()<<"updateDomainRegistryInfo success"<<domainId<<domainName<<dnInfo.Registrar;
    return true;
}

bool DbManager::updateDnCategory(int categoryId, QString domainIds)
{
        QSqlQuery query(m_mysqlConn);
        //qDebug()<<categoryId<<domainIds;
        QString sqlStr = "update domain_category_relation set categoryid ="+QString::number(categoryId)+" where domainid in ("+domainIds+")";
        query.prepare(sqlStr);
        //query.prepare("update domain_category_relation set categoryid =?  where domainid in ?");
        //query.addBindValue(categoryId);
        //query.addBindValue(domainIds);
        //qDebug()<<categoryId<<domainIds<<sqlStr;
        if (!query.exec()) {
        qDebug() << "Failed to update domain_category_relation: " << query.lastError().text();
        return false;
        }

        return true;
}

bool DbManager::updateDataDict(QString dd_key, DataDict dd) {

    QSqlQuery query(m_mysqlConn);
    query.prepare("update datadict set dd_value=?, dd_type=?,dd_desc=?  where dd_key =?");

    query.addBindValue(dd.dd_values);
    query.addBindValue(dd.dd_type);
    query.addBindValue(dd.dd_desc);
    query.addBindValue(dd.dd_key);

    if (!query.exec())
    {
        qDebug() << "Failed to datadict: " << dd.dd_key<<query.lastError().text();
        return false;
    }
    qDebug()<<"updateDataDict success"<<dd.dd_key;
    return true;

}
//对已经存在的代理服务器跳过，不存在进行插入操作
bool DbManager::updateProxyServerTable(QVector<ProxyServerData> &proxyServer)
{
        if (!m_mysqlConn.transaction()) {
        qCritical() << "Failed to begin transaction: " << m_mysqlConn.lastError().text();
        return false;
        }

        int successfulInserts = 0,fail=0;
        for (const auto &newData : proxyServer) {
        // 构造 SQL 插入语句
        QString queryStr = QString("INSERT INTO proxyServers (proxyaddr,proxytype, statusStr,updatedatetime) "
                                   "VALUES ('%1','socks5', 'NULL','%2')")
                               .arg(newData.proxyaddr).arg(newData.updatedatetime);

        // 准备并执行查询
        QSqlQuery query(m_mysqlConn);
        query.prepare(queryStr);

            // 执行插入，对唯一性冲突（如主键冲突）进行忽略处理
            if (query.exec()) {//成功插入
                ++successfulInserts;
            }
            else{
                fail++;
                if(query.lastError().nativeErrorCode()== "2067")//已存在，则Ignored
                {
                    //qWarning() << "Ignored insert due to uniqueness constraint violation: " << query.lastError().text()<<newData.proxyaddr;
                }
                else
                { // 非唯一性冲突错误，回滚事务并返回 false
                    qCritical() << "Failed to execute update query: " << query.lastError().text() << query.lastError().nativeErrorCode();
                    m_mysqlConn.rollback(); // 回滚事务
                    return false;
                }
            }
        }

        // 提交事务
        if (!m_mysqlConn.commit()) {
            qCritical() << "Failed to commit transaction: " << m_mysqlConn.lastError().text();
            return false;
        }

        qDebug() << successfulInserts << fail<<proxyServer.size()<<" records successfully inserted into ProxyServer table.";
        return true;
        //        // 开启事务，提升批量操作的效率与数据一致性
        //        if (!m_mysqlConn.transaction()) {
        //            qCritical() << "Failed to begin transaction: " << m_mysqlConn.lastError().text();
        //            return false;
        //        }
        //        for (const auto &newData : proxyServer) {

        //            QString queryStr = QString("INSERT INTO proxyServers (serverhost, port,isvalidity) VALUES ('%1', '%2',0)").arg(newData.serveraddr, newData.port);
        //            qDebug()<<queryStr;
        //            QSqlQuery query( m_mysqlConn);
        //            query.prepare(queryStr);
        //            if (!query.exec()) {
        //                qCritical() << "Failed to execute update query: " << query.lastError().text();
        //                m_mysqlConn.rollback();  // 回滚事务
        //                return false;
        //            }
        //        }
        //        // 提交事务
        //        if (!m_mysqlConn.commit()) {
        //            qCritical() << "Failed to commit transaction: " << m_mysqlConn.lastError().text();
        //            return false;
        //        }
        //        qDebug() << "ProxyServer table successfully updated.";
        //        return true;
}

bool DbManager::updateProxyServerStatus(QString proxyhost,QString updatedatetime,QString retStr)
{
    QSqlQuery query(m_mysqlConn);

    QString sqlStr = "update proxyServers set statusStr = ? ,updatedatetime =? where proxyaddr =?";
    query.prepare(sqlStr);
    query.addBindValue(retStr);
    query.addBindValue(updatedatetime);
    query.addBindValue(proxyhost);

    if (!query.exec())
    {
        qDebug() << "Failed to update proxyServers: " << query.lastError().text();
        return false;
    }
    return true;
}
